﻿USE [LibraryDataBase_wl]
GO
/****** 对象:  StoredProcedure [dbo].[Pagination_LeoWL]    脚本日期: 04/15/2010 10:12:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE    PROCEDURE [dbo].[Pagination_LeoWL]
(
@tab nvarchar(1000),---表名
@strFld nvarchar(2500), --字段字符串
@strWhere varchar(8000), --where条件 
@Page int, --页码
@RecsPerPage int, --每页容纳的记录数
@ID VARCHAR(255), --需要排序的不重复的ID号
@Sort VARCHAR(255), --排序字段及规则
@isGetCount bit--是否得到记录总数
)
AS

DECLARE @Str nVARCHAR(4000)

declare @SQL nVARCHAR(4000) --不带排序语句的SQL语句
set @SQL='select ' + @strFld + ' from '+ @tab +' where ' +@strWhere

if(@isGetCount=0)
begin

SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM 
('+@SQL+') bb  WHERE bb.'+@ID+' NOT IN (SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) 
AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') aa  ORDER BY '+@Sort+') ORDER BY '+@Sort

PRINT @Str

end

else
begin

SET @Str='select count('+@ID+') from '+ @tab + ' where ' +@strWhere

PRINT @Str
end

EXEC sp_ExecuteSql @Str


/*
存储过程: StoredProcedure [dbo].[sp_PageList]
创建者：Leo.wl
脚本日期: 04/15/2010 10:15:42 
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE   PROCEDURE [dbo].[sp_PageList]
(
@tab nvarchar(2000),---表名
@strFld nvarchar(1500), --字段字符串
@strWhere varchar(8000), --where条件 
@Page int, --页码
@RecsPerPage int, --每页容纳的记录数@RecsPerPage
--@ID VARCHAR(255), --需要排序的不重复的ID号
@Sort VARCHAR(255), --排序字段及规则
@isGetCount bit--是否得到记录总数,1为得到记录总数，0为得到数据列表
)
AS
declare @strSql nvarchar(4000)
if(@isGetCount=0)
	begin
		set nocount on;
		set @strSql=' SELECT * FROM (SELECT ROW_NUMBER() 
		OVER(ORDER BY ' + @Sort + ') AS rownum ,'+ @strFld +' FROM ' + @tab + ' where ' + @strWhere + ') AS D
		WHERE rownum BETWEEN ' + CAST(((@Page-1)*@RecsPerPage + 1) as nvarchar(20)) + ' and ' + cast((@Page*@RecsPerPage) as nvarchar(20))

	--print ((@Page-1)*@RecsPerPage + 1)CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))
	end
else
	begin
		set nocount on;
		set @strSql = 'SELECT COUNT(0) FROM '+ @tab + ' where ' +@strWhere
	end
exec (@strSql)
print @strSql

/****** 对象:  StoredProcedure [dbo].[Sp_Paging]    脚本日期: 04/15/2010 10:16:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
存储过程：Sp_Paging
创建日期：2010-04-15
创建者：Leo.wl
*/
CREATE PROCEDURE [dbo].[Sp_Paging]

@tab nvarchar(1000),---表名
@strFld nvarchar(1000), --字段字符串
@strWhere varchar(2000), --where条件 
@Page int, --页码
@RecsPerPage int, --每页容纳的记录数
@Sort VARCHAR(255), --排序字段及规则
@isGetCount bit--是否得到记录总数,1为得到记录总数，0为得到数据列表

AS
begin
declare @strSql nvarchar(4000),@countNum nvarchar(10)

set @strSql = 'select @countNum = COUNT(0)  FROM '+ @tab +' where '+@strWhere
if(@isGetCount=1)
	exec sp_executesql @strSql,N'@countNum int out',@countNum out
else
	set @countNum = '0'

set @strSql=' SELECT '+ @strFld +','+ @countNum +' as countNum FROM (SELECT ROW_NUMBER() 
		OVER(ORDER BY ' + @Sort + ') AS rownum, ' + @strFld + ' FROM ' + @tab 
		+ ' where ' + @strWhere + ') AS D WHERE rownum BETWEEN ' 
		+ cast(((@Page-1)*@RecsPerPage + 1) as nvarchar(10)) + ' and ' 
		+ cast((@Page*@RecsPerPage) as nvarchar(10))

exec (@strSql)
print @strSql
end 
